package edu.itstudy.dao.imp;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import edu.itstudy.bean.User;
import edu.itstudy.dao.IUserDao;
import edu.itstudy.utils.DBConUtil;

public class IUserDaoImp implements IUserDao{
	private Connection conn = DBConUtil.getConn();
	
	//用户注册
	public int register(User user) {	
		String sql = "insert into userinfo(name,password,age,sex,phone,address,picture,intro,recommend,flag) values(?,?,?,?,?,?,?,?,?,?)";
		int i = 0;
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, user.getName());
			pstmt.setString(2, user.getPassword());
			pstmt.setInt(3, user.getAge());
			pstmt.setString(4, user.getSex());
			pstmt.setString(5, user.getPhone());
			pstmt.setString(6, user.getAddress());
			pstmt.setString(7, user.getPicture());
			pstmt.setString(8, user.getIntro());
			pstmt.setInt(9, user.getRecommend());
			pstmt.setInt(10, user.getFlag());
			
			i = pstmt.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBConUtil.closeConn(null, null, pstmt, null);
		}
		return i;
	}
	
	//用户登录
	public int login(User user) {
		
		String sql = "select uid from userinfo where name = ? and password = ? and flag = 0";
		PreparedStatement pstmt = null;
		int uid = 0;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,user.getName());
			pstmt.setString(2,user.getPassword());
			
			ResultSet rs = pstmt.executeQuery();
			
			if(rs.next()) {
				uid = rs.getInt("uid");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBConUtil.closeConn(null, null, pstmt, null);
		}
		return uid;
		
	}
	
	//获取首页人员
	public List<User> getIndexUser(){
		String sql = "select uid,name,picture,intro from userinfo where recommend = 1 and flag = 0";
		PreparedStatement pstmt = null;
		List<User> list = null;
		try {
			pstmt = conn.prepareStatement(sql);
		
			ResultSet rs = pstmt.executeQuery();
			list = new ArrayList<User>();
			while(rs.next()) {
				int uid = rs.getInt("uid");
				String name = rs.getString("name");
				String picture = rs.getString("picture");
				String intro = rs.getString("intro");
				
				User user = new User(uid,name,picture,intro);
				list.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBConUtil.closeConn(null, null, pstmt, null);
		}
		return list;
	}
	
	//获取所有用户   
	//动态查询sql组建
	public List<User> getAllUser(User user){
		String sql = "select uid,name,age,sex,phone,address,recommend from userinfo where flag = 0 ";
		
		StringBuffer sb = new StringBuffer(sql);
		List<Object> params = new ArrayList<Object>();
		
		//动态构建本次查询的sql	
		

		if(null != user.getName() && !"".equals(user.getName())) {
			sb.append(" and name like ? ");
			params.add("%"+user.getName()+"%");
		}
		if(0 != user.getAge()) {
			sb.append(" and age >= ? ");
			params.add(user.getAge());
		}
		if(0 != user.getToage()) {
			sb.append(" and age <= ? ");
			params.add(user.getToage());
		}
		if(user.getRecommend() != 2) {
			sb.append(" and recommend = ? ");	
			params.add(user.getRecommend());
		}
		sql = sb.toString();
		
		PreparedStatement pstmt = null;
		List<User> list = null;
		try {
			pstmt = conn.prepareStatement(sql);
			//给参数赋值
			for(int i=0;i<params.size();i++) {
				pstmt.setObject(i+1, params.get(i));
			}
			
			ResultSet rs = pstmt.executeQuery();
			list = new ArrayList<User>();
			while(rs.next()) {
				if("admin".equals(rs.getString("name"))!=true)
				{
				int uid = rs.getInt("uid");
				String name = rs.getString("name");
				int age = rs.getInt("age");
				String sex = rs.getString("sex");
				String phone = rs.getString("phone");
				String address = rs.getString("address");
				int recommend = rs.getInt("recommend");
				
				User user_ = new User(uid,name,age,sex,phone,address,recommend);
				list.add(user_);
				}else
				{
					continue;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBConUtil.closeConn(null, null, pstmt, null);
		}
		return list;
	}
	
	//通过uid查询用户
	public User getUserById(int uid) {
		
		String sql = "select uid,name,password,age,sex,phone,address,picture,intro,recommend from userinfo where uid = ?";
		PreparedStatement pstmt = null;
		User user = null;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, uid);		
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()) {
				int uid_ = rs.getInt("uid");
				String name = rs.getString("name");
				String password = rs.getString("password");
				int age = rs.getInt("age");
				String sex = rs.getString("sex");
				String phone = rs.getString("phone");
				String address = rs.getString("address");
				String picture = rs.getString("picture");
				String intro = rs.getString("intro");
				int recommend = rs.getInt("recommend");
				
				user = new User(uid,name,password,age,sex,phone,address,picture,intro,recommend,0);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBConUtil.closeConn(null, null, pstmt, null);
		}
		return user;		
	}
	//用户修改
	public int updateUser(User user) {
		String sql = "update userinfo set age=?,phone=?,address=?,intro=?,recommend=? where uid=?";
		int i = 0;
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, user.getAge());
			pstmt.setString(2, user.getPhone());
			pstmt.setString(3, user.getAddress());
			pstmt.setString(4, user.getIntro());
			pstmt.setInt(5, user.getRecommend());
			pstmt.setInt(6, user.getUid());
			
			i = pstmt.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBConUtil.closeConn(null, null, pstmt, null);
		}
		return i;	
	}
	//物理删除用户
	public int deleteUserById(int uid) {
		String sql = "delete from userinfo where uid=?";
		int i = 0;
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, uid);		
			i = pstmt.executeUpdate();		
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBConUtil.closeConn(null, null, pstmt, null);
		}
		return i;	
	}
	//逻辑删除用户
	public int updateUserById(int uid,int flag) {
		String sql = "update userinfo set flag = ? where uid=?";
		int i = 0;
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, flag);
			pstmt.setInt(2, uid);		
			i = pstmt.executeUpdate();			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBConUtil.closeConn(null, null, pstmt, null);
		}
		return i;	
	}
}
